# _*_ coding: utf-8 _*_
# @Time : 2024-01-23 21:22
# @Author : wf
import openpyxl.worksheet.worksheet
from openpyxl.cell.cell import Cell
from openpyxl import Workbook
from openpyxl.formula.translate import Translator
from openpyxl.utils import FORMULAE

wb = Workbook()
ws: openpyxl.worksheet.worksheet.Worksheet = wb.active

print(FORMULAE)
print(len(FORMULAE))
print("SUM" in FORMULAE)

ws.append(['价格1', '价格2', '求和', '平均值'])
ws.append([32, 34, ])
ws.append([23, 15, ])
ws.append([455, 23, ])
ws.append([78, 12, ])

ws['c2'] = '=SUM(A2:b2)'
ws['d2'] = '=AVERAGE(A2:b2)'

# 翻译
# ws['c3'] = Translator(formula='=SUM(A2:b2)', origin='c2').translate_formula('c3')
# ws['c4'] = Translator(formula='=SUM(A2:b2)', origin='c2').translate_formula('c4')
# ws['c5'] = Translator(formula='=SUM(A2:b2)', origin='c2').translate_formula('c5')
#
# ws['d3'] = Translator(formula='=AVERAGE(A2:b2)', origin='d2').translate_formula('d3')
# ws['d4'] = Translator(formula='=AVERAGE(A2:b2)', origin='d2').translate_formula('d4')
# ws['d5'] = Translator(formula='=AVERAGE(A2:b2)', origin='d2').translate_formula('d5')

for cell in ws['c3:c5']:
    tar: openpyxl.cell.cell.Cell = cell[0]
    tar.value = Translator(formula='=SUM(A2:b2)', origin='c2').translate_formula(tar.coordinate)

for cell in ws['d3:d5']:
    tar: openpyxl.cell.cell.Cell = cell[0]
    tar.value = Translator(formula='=AVERAGE(A2:b2)', origin='d2').translate_formula(tar.coordinate)

wb.save('test3.xlsx')
